﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Web;

namespace Control.conexion
{
    class clsConexion
    {
        private DbProviderFactory mDB;
        private DbConnection mConnection;

        public clsConexion()
        {
            mConnection = CrearConexion();
        }

        string Conexion()
        {
            string ls_cadena = System.Configuration.ConfigurationManager.ConnectionStrings["cnx_cmp"].ConnectionString;
            return ls_cadena;
        }

        string Proveedor()
        {
            string ls_cadena = System.Configuration.ConfigurationManager.ConnectionStrings["cnx_cmp"].ProviderName;
            return ls_cadena;
        }

        public DbConnection CrearConexion()
        {
            DbConnection oConnection;
            mDB = DbProviderFactories.GetFactory(this.Proveedor()); //Obtener caracteristicas del proveedor sql server
            oConnection = mDB.CreateConnection();
            oConnection.ConnectionString = Conexion(); //obtener la conexion desde app.config
            return oConnection;
        }

        public DbCommand CargarComandoSP(String pStoredProcedureName, int TimeOut = 30)
        {
            DbCommand oCommand;
            oCommand = mDB.CreateCommand();
            oCommand.CommandText = pStoredProcedureName;
            oCommand.CommandType = CommandType.StoredProcedure;

            if (mConnection != null)
            { //IsNot Nothing Then
                oCommand.Connection = mConnection;
            }

            if (TimeOut != 30)
            {
                oCommand.CommandTimeout = TimeOut;
            }

            return oCommand;
        }

        public void AddParametro(DbCommand pCommand, string pParameterName, Object pParameterValue)
        {
            DbParameter oParm = mDB.CreateParameter();
            oParm.ParameterName = pParameterName;
            oParm.Value = pParameterValue;
            pCommand.Parameters.Add(oParm);
        }

        public int EjecutarTransaccion(DbCommand pCommand)
        {
            try
            {
                if (mConnection.State == ConnectionState.Closed)
                {
                    mConnection.Open();
                }
                pCommand.Connection = mConnection;
                return pCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new System.Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (mConnection.State == ConnectionState.Open)
                {
                    mConnection.Close();
                }
            }
        }

        public DataTable EjecutarConsulta(DbCommand pCommand)
        {
            DataTable oDT = new DataTable();
            try
            {
                DbDataAdapter oDA = mDB.CreateDataAdapter();
                pCommand.Connection = mConnection;

                oDA.SelectCommand = pCommand;
                oDA.Fill(oDT);

                return oDT;
            }
            catch (Exception ex)
            {
                throw new System.Exception(ex.Message, ex.InnerException);
            }
        }

        public void AbrirConexion()
        {
            if ((mConnection != null) && (mConnection.State == ConnectionState.Closed))
            {
                mConnection.Open();
            }
        }

        public void CerrarConexion()
        {
            if ((mConnection != null) && (mConnection.State != ConnectionState.Closed))
            {
                mConnection.Close();
            }
        }
    }
}
